------------------------------------------------------------------------
-- This file is part of a Microsoft SQL Server Shared Source Application.
-- Copyright (C) Microsoft Corporation.  All rights reserved.
 
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
-- KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
------------------------------------------------------------------------


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'LoadTest')
	DROP DATABASE [LoadTest]
GO

CREATE DATABASE [LoadTest] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'LoadTest', N'autoclose', N'false'
GO

exec sp_dboption N'LoadTest', N'bulkcopy', N'false'
GO

exec sp_dboption N'LoadTest', N'trunc. log', N'false'
GO

exec sp_dboption N'LoadTest', N'torn page detection', N'true'
GO

exec sp_dboption N'LoadTest', N'read only', N'false'
GO

exec sp_dboption N'LoadTest', N'dbo use', N'false'
GO

exec sp_dboption N'LoadTest', N'single', N'false'
GO

exec sp_dboption N'LoadTest', N'autoshrink', N'false'
GO

exec sp_dboption N'LoadTest', N'ANSI null default', N'false'
GO

exec sp_dboption N'LoadTest', N'recursive triggers', N'false'
GO

exec sp_dboption N'LoadTest', N'ANSI nulls', N'false'
GO

exec sp_dboption N'LoadTest', N'concat null yields null', N'false'
GO

exec sp_dboption N'LoadTest', N'cursor close on commit', N'false'
GO

exec sp_dboption N'LoadTest', N'default to local cursor', N'false'
GO

exec sp_dboption N'LoadTest', N'quoted identifier', N'false'
GO

exec sp_dboption N'LoadTest', N'ANSI warnings', N'false'
GO

exec sp_dboption N'LoadTest', N'auto create statistics', N'true'
GO

exec sp_dboption N'LoadTest', N'auto update statistics', N'true'
GO

use [LoadTest]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestC__LoadT__0EA330E9]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestCase] DROP CONSTRAINT FK__LoadTestC__LoadT__0EA330E9
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestM__LoadT__0DAF0CB0]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestMessage] DROP CONSTRAINT FK__LoadTestM__LoadT__0DAF0CB0
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestP__LoadT__0F975522]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestPerformanceCounter] DROP CONSTRAINT FK__LoadTestP__LoadT__0F975522
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestP__LoadT__108B795B]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestPerformanceCounterCategory] DROP CONSTRAINT FK__LoadTestP__LoadT__108B795B
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestP__LoadT__117F9D94]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestPerformanceCounterInstance] DROP CONSTRAINT FK__LoadTestP__LoadT__117F9D94
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestP__LoadT__1273C1CD]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestPerformanceCounterSample] DROP CONSTRAINT FK__LoadTestP__LoadT__1273C1CD
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestR__LoadT__1367E606]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestRunAgent] DROP CONSTRAINT FK__LoadTestR__LoadT__1367E606
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestR__LoadT__145C0A3F]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestRunInterval] DROP CONSTRAINT FK__LoadTestR__LoadT__145C0A3F
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LoadTestS__LoadT__15502E78]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LoadTestScenario] DROP CONSTRAINT FK__LoadTestS__LoadT__15502E78
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__WebLoadTe__LoadT__164452B1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WebLoadTestErrorDetail] DROP CONSTRAINT FK__WebLoadTe__LoadT__164452B1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__WebLoadTe__LoadT__182C9B23]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WebLoadTestRequestMap] DROP CONSTRAINT FK__WebLoadTe__LoadT__182C9B23
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__WebLoadTe__LoadT__2F10007B]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WebLoadTestTransaction] DROP CONSTRAINT FK__WebLoadTe__LoadT__2F10007B
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesByRequest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesByRequest]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesByAgent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesByAgent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesByTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesByTest]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesByType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesByType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesAll]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetErrorDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetErrorDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetTestCases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetTestCases]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_DeleteLoadTestRun]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_DeleteLoadTestRun]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetAgents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetAgents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetInstanceSummaries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetInstanceSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetMessagesTypeSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetMessagesTypeSummary]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetRequestMap]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetRequestMap]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetSamplesForInstance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetSamplesForInstance]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetFirstSampleForInstance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetFirstSampleForInstance]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetLastSampleForInstance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetLastSampleForInstance]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetTransactionMap]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetTransactionMap]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_InsertSample]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_InsertSample]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_InsertSqlTraceRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_InsertSqlTraceRow]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdateInstanceSummaryData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdateInstanceSummaryData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_GetCompletedLoadTestRuns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_GetCompletedLoadTestRuns]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_InsertTestDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_InsertTestDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_InsertTransactionDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_InsertTransactionDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_InsertPageDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_InsertPageDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdateTestPercentiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdateTestPercentiles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdateTransactionPercentiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdateTransactionPercentiles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdatePagePercentiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdatePagePercentiles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdatePageByNetworkPercentiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdatePageByNetworkPercentiles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Prc_UpdateSummaryData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Prc_UpdateSummaryData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestComputedCounterSamples]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestComputedCounterSamples]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestMessageView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestMessageView]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestWebRequestCounterSamples]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestWebRequestCounterSamples]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageSamples]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestPageSamples]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTransactionSamples]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestTransactionSamples]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTransactionSummaries]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestTransactionSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestWebRequestCounterSummaries]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestWebRequestCounterSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageSummaries]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestPageSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTestCaseSamples]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestTestCaseSamples]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTestCaseSummaries]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestTestCaseSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestComputedCounterSummaries]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LoadTestComputedCounterSummaries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTestResults]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view LoadTestTestResults
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTransactionResults]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view LoadTestTransactionResults
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageResults]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view LoadTestPageResults
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageResultsByNetwork]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view LoadTestPageResultsByNetwork
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestCase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestCase]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestThresholdMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestThresholdMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPerformanceCounter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPerformanceCounter]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPerformanceCounterCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPerformanceCounterCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPerformanceCounterInstance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPerformanceCounterInstance]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPerformanceCounterSample]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPerformanceCounterSample]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestRunAgent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestRunAgent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestRunInterval]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestRunInterval]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestScenario]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestScenario]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WebLoadTestErrorDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WebLoadTestErrorDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WebLoadTestRequestMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WebLoadTestRequestMap]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WebLoadTestTransaction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WebLoadTestTransaction]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestSqlTrace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestSqlTrace]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestBrowsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestBrowsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestNetworks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestNetworks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTestDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestTestDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPageDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTransactionDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestTransactionDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTestSummaryData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestTestSummaryData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestTransactionSummaryData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestTransactionSummaryData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageSummaryData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPageSummaryData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestPageSummaryByNetwork]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestPageSummaryByNetwork]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadTestRun]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTestRun]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WebLoadTestTransactionContent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [WebLoadTestTransactionContent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertTrans') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertTrans
GO

CREATE TABLE [dbo].[LoadTestRun] (
	[LoadTestRunId] [int] IDENTITY (1, 1) NOT NULL ,
	[LoadTestName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RunId] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartTime] [datetime] NULL ,
	[EndTime] [datetime] NULL ,
	[RunDuration] [int] NOT NULL ,
	[WarmupTime] [int] NOT NULL ,
	[RunSettingUsed] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsLocalRun] [bit] NOT NULL ,
	[ControllerName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Outcome] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LoadTest] [image] NULL ,
	[Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestRun] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestCase] (
	[LoadTestRunId] [int] NOT NULL ,
	[ScenarioId] [int] NOT NULL ,
	[TestCaseId] [int] NOT NULL ,
	[TestCaseName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestCase] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestMessage] (
	[LoadTestRunId] [int] NOT NULL ,
	[AgentId] [int] NOT NULL ,
	[MessageId] [int] NOT NULL ,
	[MessageType] [tinyint] NOT NULL ,
	[MessageText] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SubType] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StackTrace] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MessageTimeStamp] [datetime] NOT NULL ,
	[TestCaseId] [int] NULL ,
	[RequestId] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestMessage] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestThresholdMessage] (
	[LoadTestRunId] [int] NOT NULL ,
	[TestRunIntervalId] [int] NOT NULL,
	[CounterInstanceId] [int] NOT NULL,
	[MessageId] [int] NOT NULL ,
	[MessageText] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestThresholdMessage] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPerformanceCounter] (
	[LoadTestRunId] [int] NOT NULL ,
	[CounterCategoryId] [int] NOT NULL ,
	[CounterId] [int] NOT NULL ,
	[CounterName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPerformanceCounter] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPerformanceCounterCategory] (
	[LoadTestRunId] [int] NOT NULL ,
	[CounterCategoryId] [int] NOT NULL ,
	[CategoryName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MachineName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StartTimeStamp100nSec] [bigint] NOT NULL	
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPerformanceCounterCategory] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPerformanceCounterInstance] (
	[LoadTestRunId] [int] NOT NULL ,
	[CounterId] [int] NOT NULL ,
	[InstanceId] [int] NOT NULL ,
	[LoadTestItemId] [int] NULL ,
	[InstanceName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CumulativeValue] [real] NULL ,
	[OverallThresholdRuleResult] [tinyint] NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPerformanceCounterInstance] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPerformanceCounterSample] (
	[LoadTestRunId] [int] NOT NULL ,
	[TestRunIntervalId] [int] NOT NULL ,
	[InstanceId] [int] NOT NULL ,
	[ComputedValue] [real] NULL ,
	[RawValue] [bigint] NOT NULL ,
	[BaseValue] [bigint] NOT NULL ,
	[CounterFrequency] [bigint] NOT NULL ,
	[SystemFrequency] [bigint] NOT NULL ,
	[SampleTimeStamp] [bigint] NOT NULL ,
	[SampleTimeStamp100nSec] [bigint] NOT NULL ,
	[CounterType] [int] NOT NULL ,
	[ThresholdRuleResult] [tinyint] NOT NULL ,
	[ThresholdRuleMessageId] [int] NULL
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPerformanceCounterSample] TO PUBLIC
GO


CREATE TABLE [dbo].[LoadTestRunAgent] (
	[LoadTestRunId] [int] NOT NULL ,
	[AgentId] [int] NOT NULL ,
	[AgentName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestRunAgent] TO PUBLIC
GO


CREATE TABLE [dbo].[LoadTestRunInterval] (
	[LoadTestRunId] [int] NOT NULL ,
	[TestRunIntervalId] [int] NOT NULL ,
	[IntervalStartTime] [datetime] NOT NULL ,
	[IntervalEndTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestRunInterval] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestScenario] (
	[LoadTestRunId] [int] NOT NULL ,
	[ScenarioId] [int] NOT NULL ,
	[ScenarioName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT INSERT, DELETE, SELECT ON [dbo].[LoadTestScenario] TO PUBLIC
GO


CREATE TABLE [dbo].[WebLoadTestErrorDetail] (
	[LoadTestRunId] [int] NOT NULL ,
	[AgentId] [int] NOT NULL ,
	[MessageId] [int] NOT NULL ,
	[WebTestRequestResult] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[WebLoadTestErrorDetail] TO PUBLIC
GO


CREATE TABLE [dbo].[WebLoadTestRequestMap] (
	[LoadTestRunId] [int] NOT NULL ,
	[RequestId] [int] NOT NULL ,
	[TestCaseId] [int] NOT NULL ,
	[RequestUri] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[WebLoadTestRequestMap] TO PUBLIC
GO

CREATE TABLE [dbo].[WebLoadTestTransaction] (
	[LoadTestRunId] [int] NOT NULL ,
	[TransactionId] [int] NOT NULL ,
	[TestCaseId] [int] NOT NULL ,
	[TransactionName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[WebLoadTestTransaction] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestSqlTrace] (
	[LoadTestRunId] [int] NOT NULL ,
	[TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Duration] [bigint] NULL ,
	[StartTime] [datetime] NULL ,
	[EndTime] [datetime] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[CPU] [int] NULL ,
	[EventClass] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestSqlTrace] TO PUBLIC
GO

-- New tables added for Result Details

CREATE TABLE [dbo].[LoadTestBrowsers] (
	[LoadTestRunId] [int] NOT NULL ,
	[BrowserId] [int] NOT NULL ,
	[BrowserName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestBrowsers] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[BrowserId]
	)  ON [PRIMARY] 
GO
	
GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestBrowsers] TO PUBLIC
GO
	
CREATE TABLE [dbo].[LoadTestNetworks] (
	[LoadTestRunId] [int] NOT NULL ,
	[NetworkId] [int] NOT NULL ,
	[NetworkName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestNetworks] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[NetworkId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestNetworks] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestTestDetail] (
	[LoadTestRunId] [int] NOT NULL ,
	[TestDetailId] [int] NOT NULL ,
	[TimeStamp] [datetime] NOT NULL ,
	[TestCaseId] [int] NOT NULL ,
	[ElapsedTime] [float] NOT NULL,
        [AgentId] [int] NOT NULL,
        [BrowserId] [int],
        [NetworkId] [int]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestTestDetail] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TestDetailId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestTestDetail] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPageDetail] (
	[LoadTestRunId] [int] NOT NULL ,
	[PageDetailId] [int] NOT NULL ,
	[TestDetailId] [int] NOT NULL ,
	[TimeStamp] [datetime] NOT NULL ,
	[PageId] [int] NOT NULL ,
	[ResponseTime] [float] NOT NULL,
	[ResponseTimeGoal] [float] NOT NULL,
	[GoalExceeded] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestPageDetail] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[PageDetailId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPageDetail] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestTransactionDetail] (
	[LoadTestRunId] [int] NOT NULL ,
	[TransactionDetailId] [int] NOT NULL ,
	[TestDetailId] [int] NOT NULL ,
	[TimeStamp] [datetime] NOT NULL ,
	[TransactionId] [int] NOT NULL ,
	[ElapsedTime] [float] NOT NULL	
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestTransactionDetail] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TransactionDetailId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestTransactionDetail] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestTestSummaryData] (
	[LoadTestRunId] [int] NOT NULL ,
	[TestCaseId] [int] NOT NULL ,
	[TestsRun] [int] NOT NULL ,
	[Average] [float] NOT NULL ,
	[Minimum] [float] NOT NULL ,
	[Maximum] [float] NOT NULL ,
	[Percentile90] [float] NULL ,
	[Percentile95] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestTestSummaryData] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TestCaseId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestTestSummaryData] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestTransactionSummaryData] (
	[LoadTestRunId] [int] NOT NULL ,
	[TransactionId] [int] NOT NULL ,
	[TransactionCount] [int] NOT NULL ,
	[Average] [float] NOT NULL ,
	[Minimum] [float] NOT NULL ,
	[Maximum] [float] NOT NULL ,
	[Percentile90] [float] NULL ,
	[Percentile95] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestTransactionSummaryData] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TransactionId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestTransactionSummaryData] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPageSummaryData] (
	[LoadTestRunId] [int] NOT NULL ,
	[PageId] [int] NOT NULL ,
	[PageCount] [int] NOT NULL ,
	[Average] [float] NOT NULL ,
	[Minimum] [float] NOT NULL ,
	[Maximum] [float] NOT NULL ,
	[Percentile90] [float] NULL ,
	[Percentile95] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestPageSummaryData] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[PageId]
	)  ON [PRIMARY] 
GO

GRANT SELECT, INSERT, UPDATE ON [dbo].[LoadTestPageSummaryData] TO PUBLIC
GO

CREATE TABLE [dbo].[LoadTestPageSummaryByNetwork] (
	[LoadTestRunId] [int] NOT NULL ,
	[PageId] [int] NOT NULL ,
	[NetworkId] [int] NOT NULL ,
	[PageCount] [int] NOT NULL ,
	[Average] [float] NOT NULL ,
	[Minimum] [float] NOT NULL ,
	[Maximum] [float] NOT NULL ,
	[Percentile90] [float] NULL ,
	[Percentile95] [float] NULL ,
	[Goal] [float] NULL,
	[PagesMeetingGoal] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoadTestPageSummaryByNetwork] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[PageId],
		[NetworkId]
	)  ON [PRIMARY]
GO


ALTER TABLE [dbo].[LoadTestRun] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestCase] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TestCaseId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestMessage] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[AgentId],
		[MessageId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestThresholdMessage] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[MessageId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounter] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[CounterId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterCategory] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[CounterCategoryId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterInstance] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[InstanceId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterSample] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TestRunIntervalId],
		[InstanceId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestRunAgent] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[AgentId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestRunInterval] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TestRunIntervalId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LoadTestScenario] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[ScenarioId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WebLoadTestErrorDetail] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[AgentId],
		[MessageId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WebLoadTestRequestMap] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[RequestId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WebLoadTestTransaction] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LoadTestRunId],
		[TransactionId]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [LoadTestCounterNameIndex] ON [dbo].[LoadTestPerformanceCounter]([CounterName]) ON [PRIMARY]
GO

 CREATE  INDEX [LoadTestCategoryNameIndex] ON [dbo].[LoadTestPerformanceCounterCategory]([CategoryName]) ON [PRIMARY]
GO

 CREATE  INDEX [LoadTestInstanceNameIndex] ON [dbo].[LoadTestPerformanceCounterInstance]([InstanceName]) ON [PRIMARY]
GO

 CREATE  INDEX [LoadTestSampleInstanceIndex] ON [dbo].[LoadTestPerformanceCounterSample]([LoadTestRunId], [InstanceId]) ON [PRIMARY]
GO

 CREATE INDEX [LoadTestSqlTraceIndex] ON [dbo].[LoadTestSqlTrace]([LoadTestRunId]) ON [PRIMARY]
GO

-- Create indices on detail tables

CREATE NONCLUSTERED INDEX [LoadTestTestDetail4] ON [dbo].[LoadTestTestDetail] ([LoadTestRunId] ASC, [TestCaseId] ASC, [ElapsedTime] ASC)
GO

CREATE NONCLUSTERED INDEX [LoadTestTransactionDetail4] ON [dbo].[LoadTestTransactionDetail] ([LoadTestRunId] ASC, [TransactionId] ASC, [ElapsedTime] ASC, [TestDetailId] ASC )
GO

CREATE NONCLUSTERED INDEX [LoadTestPageDetail4] ON [dbo].[LoadTestPageDetail] ([LoadTestRunId] ASC, [PageId] ASC, [ResponseTime] ASC, [TestDetailId] ASC )
GO

/* Statistics to support recommendations */
CREATE STATISTICS [hind_98099390_6A] ON [dbo].[loadtestpagedetail] ([responsetime])
GO

CREATE STATISTICS [hind_98099390_1A_3A_5A_6D] ON [dbo].[loadtestpagedetail] ([loadtestrunid], [testdetailid], [pageid], [responsetime])
GO

ALTER TABLE [dbo].[LoadTestCase] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestMessage] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestThresholdMessage] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounter] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterCategory] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterInstance] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestPerformanceCounterSample] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestRunAgent] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestRunInterval] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestScenario] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[WebLoadTestErrorDetail] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[WebLoadTestRequestMap] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[WebLoadTestTransaction] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

ALTER TABLE [dbo].[LoadTestSqlTrace] ADD 
	 FOREIGN KEY 
	(
		[LoadTestRunId]
	) REFERENCES [dbo].[LoadTestRun] (
		[LoadTestRunId]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE VIEW [dbo].[LoadTestComputedCounterSample] AS
SELECT category.LoadTestRunId, category.MachineName, category.CategoryName, counter.CounterName, 
    instance.InstanceName, interval.IntervalStartTime, interval.IntervalEndTime, 
    countersample.CounterType, countersample.ComputedValue, countersample.RawValue, countersample.ThresholdRuleResult
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
WHERE countersample.ComputedValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestComputedCounterSample] TO PUBLIC
GO


CREATE VIEW [dbo].[LoadTestMessageView] AS
SELECT 
message.LoadTestRunId,
agent.AgentName,
scenario.ScenarioName,
testcase.TestCaseName,
requestmap.RequestUri,
message.MessageId,
message.MessageTimeStamp,
message.MessageType,
message.SubType,
message.MessageText,
message.StackTrace,
message.RequestId,
HasWebTestErrorDetail = 
    CASE ISNULL(detail.MessageId,-1)
	WHEN -1 THEN 'false'
	ELSE 'true'
    END
FROM LoadTestMessage as message
LEFT OUTER JOIN LoadTestCase AS testcase
    ON message.LoadTestRunId = testcase.LoadTestRunId
    AND message.TestCaseId = testcase.TestCaseId
LEFT OUTER JOIN LoadTestScenario AS scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
LEFT OUTER JOIN LoadTestRunAgent AS agent
    ON message.LoadTestRunId = agent.LoadTestRunId
    AND message.AgentId = agent.AgentId
LEFT OUTER JOIN WebLoadTestRequestMap AS requestmap
    ON message.LoadTestRunId = requestmap.LoadTestRunId
    AND message.RequestId = requestmap.RequestId        
LEFT OUTER JOIN WebLoadTestErrorDetail AS detail
    ON message.LoadTestRunId = detail.LoadTestRunId
    AND message.MessageId = detail.MessageId
    AND message.AgentId = detail.AgentId        

GO
GRANT SELECT ON [dbo].[LoadTestMessageView] TO PUBLIC
GO


CREATE VIEW [dbo].[LoadTestMessageSummary] AS
SELECT 
message.LoadTestRunId,
agent.AgentName,
scenario.ScenarioName,
testcase.TestCaseName,
requestmap.RequestUri,
message.MessageId,
message.MessageTimeStamp,
MessageType = 
    CASE message.MessageType
        WHEN 0 THEN 'TestError'
        WHEN 1 THEN 'Exception'
        WHEN 2 THEN 'HttpError'
        WHEN 3 THEN 'ValidationRuleError'
        WHEN 4 THEN 'ExtractionRuleError'
        WHEN 5 THEN 'Timeout'
        ELSE 'Unknown'
    END,
message.SubType,
message.MessageText,
message.StackTrace,
message.RequestId,
HasWebTestErrorDetail = 
    CASE ISNULL(detail.MessageId,-1)
      WHEN -1 THEN 'false'
      ELSE 'true'
    END
FROM LoadTestMessage as message
LEFT OUTER JOIN LoadTestCase AS testcase
    ON message.LoadTestRunId = testcase.LoadTestRunId
    AND message.TestCaseId = testcase.TestCaseId
LEFT OUTER JOIN LoadTestScenario AS scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
LEFT OUTER JOIN LoadTestRunAgent AS agent
    ON message.LoadTestRunId = agent.LoadTestRunId
    AND message.AgentId = agent.AgentId
LEFT OUTER JOIN WebLoadTestRequestMap AS requestmap
    ON message.LoadTestRunId = requestmap.LoadTestRunId
    AND message.RequestId = requestmap.RequestId        
LEFT OUTER JOIN WebLoadTestErrorDetail AS detail
    ON message.LoadTestRunId = detail.LoadTestRunId
    AND message.MessageId = detail.MessageId
    AND message.AgentId = detail.AgentId 
GO

GRANT SELECT ON [dbo].[LoadTestMessageSummary] TO PUBLIC
GO


CREATE  VIEW [dbo].[LoadTestWebRequestCounterSample] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, request.RequestUri, 
    interval.IntervalStartTime, interval.IntervalEndTime,
    countersample.CounterType, countersample.ComputedValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
INNER JOIN WebLoadTestRequestMap AS request
    ON request.LoadTestRunId = instance.LoadTestRunId
    AND request.RequestId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON request.LoadTestRunId = testcase.LoadTestRunId
    AND request.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Request' and countersample.ComputedValue IS NOT NULL

GO

GRANT SELECT ON [dbo].[LoadTestWebRequestCounterSample] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestPageSample] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, request.RequestUri, 
    interval.IntervalStartTime, interval.IntervalEndTime,
    countersample.ComputedValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
INNER JOIN WebLoadTestRequestMap AS request
    ON request.LoadTestRunId = instance.LoadTestRunId
    AND request.RequestId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON request.LoadTestRunId = testcase.LoadTestRunId
    AND request.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Page' and countersample.ComputedValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestPageSample] TO PUBLIC
GO
 
CREATE VIEW [dbo].[LoadTestWebPageAndUserLoadCounterSample] AS

Select
	counter.loadtestrunid,
	instance.instanceName,
	request.RequestUri,
	interval.IntervalStartTime,
	(Select ComputedValue from LoadTestComputedCounterSample
		where (CounterName = 'User Load' and instanceName = '_total') 
		AND intervalstartTime = interval.IntervalStartTime) As UserLoad,
	countersample.ComputedValue As ResponseTime,
	(Select ComputedValue from LoadTestComputedCounterSample 
		where (countername = 'Requests / sec' and instanceName = instance.instancename)
		AND intervalstartTime = interval.IntervalStartTime) as RPS
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
INNER JOIN WebLoadTestRequestMap AS request
    ON request.LoadTestRunId = instance.LoadTestRunId
    AND request.RequestId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON request.LoadTestRunId = testcase.LoadTestRunId
    AND request.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE counter.countername = 'Page Response Time' and 
countersample.ComputedValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestWebPageAndUserLoadCounterSample] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestTransactionSample] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, transactions.TransactionName, 
    interval.IntervalStartTime, interval.IntervalEndTime,
    countersample.ComputedValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
INNER JOIN WebLoadTestTransaction AS transactions
    ON transactions.LoadTestRunId = instance.LoadTestRunId
    AND transactions.TransactionId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON transactions.LoadTestRunId = testcase.LoadTestRunId
    AND transactions.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Transaction' and countersample.ComputedValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestTransactionSample] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestTransactionSummary] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, transactions.TransactionName, 
    instance.CumulativeValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN WebLoadTestTransaction AS transactions
    ON transactions.LoadTestRunId = instance.LoadTestRunId
    AND transactions.TransactionId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON transactions.LoadTestRunId = testcase.LoadTestRunId
    AND transactions.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Transaction' and instance.CumulativeValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestTransactionSummary] TO PUBLIC
GO

CREATE  VIEW [dbo].[LoadTestWebRequestCounterSummary] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, request.RequestUri, 
    instance.CumulativeValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN WebLoadTestRequestMap AS request
    ON request.LoadTestRunId = instance.LoadTestRunId
    AND request.RequestId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON request.LoadTestRunId = testcase.LoadTestRunId
    AND request.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Request' and instance.CumulativeValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestWebRequestCounterSummary] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestPageSummary] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, request.RequestUri, 
    instance.CumulativeValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN WebLoadTestRequestMap AS request
    ON request.LoadTestRunId = instance.LoadTestRunId
    AND request.RequestId = instance.LoadTestItemId
INNER JOIN LoadTestCase As testcase
    ON request.LoadTestRunId = testcase.LoadTestRunId
    AND request.TestCaseId = testcase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Page' and instance.CumulativeValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestPageSummary] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestComputedCounterSummary] AS
SELECT category.LoadTestRunId, category.MachineName, category.CategoryName, counter.CounterName, 
    instance.InstanceName, instance.CumulativeValue, instance.OverallThresholdRuleResult
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
WHERE instance.cumulativeValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestComputedCounterSummary] TO PUBLIC
GO

CREATE VIEW [dbo].[LoadTestTestCaseSample] AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName,  
    interval.IntervalStartTime, interval.IntervalEndTime,
    countersample.ComputedValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestPerformanceCounterSample AS countersample 
    ON countersample.InstanceId = instance.InstanceId
    AND countersample.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestRunInterval AS interval 
    ON interval.LoadTestRunId = countersample.LoadTestRunId
    AND interval.TestRunIntervalId = countersample.TestRunIntervalId
INNER JOIN LoadTestCase As testcase
    ON testcase.LoadTestRunId = instance.LoadTestRunId
    AND testcase.TestCaseId = instance.LoadTestItemId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Test' and countersample.ComputedValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestTestCaseSample] TO PUBLIC
GO

CREATE VIEW LoadTestTestCaseSummary AS
SELECT category.LoadTestRunId, counter.CounterName,
    scenario.ScenarioName, testcase.TestCaseName, 
    instance.CumulativeValue
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
INNER JOIN LoadTestCase As testcase
    ON counter.LoadTestRunId = testcase.LoadTestRunId
    AND testcase.TestCaseId = instance.LoadTestItemId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE category.CategoryName = 'LoadTest:Test' and instance.CumulativeValue IS NOT NULL
GO

GRANT SELECT ON [dbo].[LoadTestTestCaseSummary] TO PUBLIC
GO

-- New views

CREATE VIEW LoadTestTestResults AS 
SELECT
    testSummary.LoadTestRunId, 
    scenario.ScenarioName,
    testCase.TestCaseName,  
    testSummary.TestsRun, 
    testSummary.Minimum, 
    testSummary.Average,
    testSummary.Percentile90, 
    testSummary.Percentile95, 
    testSummary.Maximum
FROM LoadTestTestSummaryData AS testSummary 
INNER JOIN LoadTestCase as testCase
    ON testSummary.LoadTestRunId = testCase.LoadTestRunId
    AND testSummary.TestCaseId = testCase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
GO

GRANT SELECT ON [dbo].LoadTestTestResults TO PUBLIC
GO

CREATE VIEW LoadTestTransactionResults AS 
SELECT
    transactionSummary.LoadTestRunId, 
    scenario.ScenarioName,
    testCase.TestCaseName, 
    transactions.TransactionName,
    transactionSummary.TransactionCount, 
    transactionSummary.Minimum, 
    transactionSummary.Average,
    transactionSummary.Percentile90, 
    transactionSummary.Percentile95, 
    transactionSummary.Maximum
FROM LoadTestTransactionSummaryData AS transactionSummary 
INNER JOIN WebLoadTestTransaction AS transactions 
    ON transactionSummary.LoadTestRunId = transactions.LoadTestRunId
    AND transactionSummary.TransactionId = transactions.TransactionId
INNER JOIN LoadTestCase as testCase
    ON transactions.LoadTestRunId = testCase.LoadTestRunId
    AND transactions.TestCaseId = testCase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
GO

GRANT SELECT ON [dbo].LoadTestTransactionResults TO PUBLIC
GO

CREATE VIEW LoadTestPageResults AS 
SELECT
    pageSummary.LoadTestRunId, 
    scenario.ScenarioName,
    testCase.TestCaseName, 
    requestMap.RequestUri, 
    pageSummary.PageCount, 
    pageSummary.Minimum, 
    pageSummary.Average,
    pageSummary.Percentile90, 
    pageSummary.Percentile95, 
    pageSummary.Maximum
FROM LoadTestPageSummaryData AS pageSummary 
INNER JOIN WebLoadTestRequestMap AS requestMap 
    ON pageSummary.LoadTestRunId = requestMap.LoadTestRunId
    AND pageSummary.PageId = requestMap.requestId
INNER JOIN LoadTestCase as testCase
    ON requestMap.LoadTestRunId = testCase.LoadTestRunId
    AND requestMap.TestCaseId = testCase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
GO

GRANT SELECT ON [dbo].LoadTestPageResults TO PUBLIC
GO

CREATE VIEW LoadTestPageResultsByNetwork AS 
SELECT
    pageSummary.LoadTestRunId,
    scenario.ScenarioName,
    testCase.TestCaseName, 
    requestMap.RequestUri,
    networks.NetworkName,
    pageSummary.PageCount,
    pageSummary.Minimum,
    pageSummary.Average,
    pageSummary.Percentile90,
    pageSummary.Percentile95,
    pageSummary.Maximum,
    pageSummary.Goal,
    pageSummary.PagesMeetingGoal
FROM LoadTestPageSummaryByNetwork AS pageSummary
INNER JOIN WebLoadTestRequestMap AS requestMap 
    ON pageSummary.LoadTestRunId = requestMap.LoadTestRunId
    AND pageSummary.PageId = requestMap.requestId
INNER JOIN LoadTestCase as testCase
    ON requestMap.LoadTestRunId = testCase.LoadTestRunId
    AND requestMap.TestCaseId = testCase.TestCaseId
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
INNER JOIN LoadTestNetworks as networks
    ON pageSummary.LoadTestRunId = networks.LoadTestRunId
    AND pageSummary.NetworkId = networks.NetworkId
GO

GRANT SELECT ON [dbo].LoadTestPageResultsByNetwork TO PUBLIC
GO

CREATE PROCEDURE Prc_GetCompletedLoadTestRuns
AS
SELECT LoadTestRunId, 
	RunId,
	LoadTestName,
	Description,
	StartTime,
	EndTime,
	IsLocalRun,
	ControllerName
FROM LoadTestRun
WHERE 
LoadTestName IS NOT NULL AND 
StartTime IS NOT NULL AND
EndTime IS NOT NULL
ORDER BY LoadTestRunId

GRANT EXECUTE ON Prc_GetCompletedLoadTestRuns TO PUBLIC
GO


CREATE PROCEDURE Prc_DeleteLoadTestRun @LoadTestRunId int
AS
BEGIN
DELETE FROM LoadTestBrowsers WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestNetworks WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestTestDetail WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPageDetail WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestTransactionDetail WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestTestSummaryData WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestTransactionSummaryData WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPageSummaryData WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPageSummaryByNetwork WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestCase WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestMessage WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestThresholdMessage WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPerformanceCounter WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPerformanceCounterCategory WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPerformanceCounterInstance WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestRunAgent WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestRunInterval WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestScenario WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestSqlTrace WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM WebLoadTestErrorDetail WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM WebLoadTestTransaction WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM WebLoadTestRequestMap WHERE LoadTestRunId = @LoadTestRunId
DELETE FROM LoadTestRun WHERE LoadTestRunId = @LoadTestRunId
END
GO

CREATE PROCEDURE Prc_GetAgents @LoadTestRunId int
AS
SELECT AgentId, AgentName 
FROM LoadTestRunAgent
WHERE LoadTestRunId = @LoadTestRunId
ORDER BY AgentId
GO

GRANT EXECUTE ON Prc_GetAgents TO PUBLIC
GO

CREATE PROCEDURE Prc_GetCategories @LoadTestRunId int
AS
SELECT CounterCategoryId, CategoryName, MachineName, StartTimeStamp100nSec
FROM LoadTestPerformanceCounterCategory
WHERE LoadTestRunId = @LoadTestRunId
ORDER BY CounterCategoryId
GO

GRANT EXECUTE ON Prc_GetCategories TO PUBLIC
GO

CREATE PROCEDURE Prc_GetInstanceSummaries @LoadTestRunId int
AS
SELECT 
instance.InstanceId, 
category.MachineName, 
category.CategoryName, 
counter.CounterName, 
instance.InstanceName,
instance.CumulativeValue,
instance.OverallThresholdRuleResult
FROM LoadTestPerformanceCounterCategory AS category 
INNER JOIN LoadTestPerformanceCounter AS counter 
    ON category.LoadTestRunId = counter.LoadTestRunId
    AND category.CounterCategoryId = counter.CounterCategoryId
INNER JOIN LoadTestPerformanceCounterInstance AS instance 
    ON counter.CounterId = instance.CounterId
    AND counter.LoadTestRunId = instance.LoadTestRunId
WHERE category.LoadTestRunId = @LoadTestRunId
ORDER BY category.MachineName, category.CategoryName, counter.CounterName, instance.InstanceName
GO

GRANT EXECUTE ON Prc_GetInstanceSummaries TO PUBLIC
GO

CREATE PROCEDURE Prc_GetMessagesTypeSummary
	@LoadTestRunId int
AS
SELECT DISTINCT MessageType, SubType, COUNT(*) as Count
FROM LoadTestMessage
WHERE LoadTestRunId = @LoadTestRunId
GROUP BY MessageType, SubType
ORDER BY Count DESC

GO
GRANT EXECUTE ON Prc_GetMessagesTypeSummary TO PUBLIC
GO

CREATE PROCEDURE Prc_GetRequestMap @LoadTestRunId int
AS
SELECT 
RequestId,
TestCaseId,
RequestUri
FROM WebLoadTestRequestMap
WHERE LoadTestRunId = @LoadTestRunId
ORDER BY RequestId
GO

GRANT EXECUTE ON Prc_GetRequestMap TO PUBLIC
GO


CREATE PROCEDURE Prc_GetSamplesForInstance @LoadTestRunId int, @InstanceId int
AS
SELECT
interval.IntervalEndTime,
samples.RawValue, 
samples.BaseValue, 
samples.CounterFrequency, 
samples.SystemFrequency,
samples.SampleTimeStamp,
samples.SampleTimeStamp100nSec,
samples.CounterType,
samples.ThresholdRuleResult
FROM LoadTestPerformanceCounterSample as samples
LEFT OUTER JOIN LoadTestRunInterval AS interval
    ON samples.LoadTestRunId = interval.LoadTestRunId
    AND samples.TestRunIntervalId = interval.TestRunIntervalId
WHERE samples.LoadTestRunId = @LoadTestRunId
AND samples.InstanceId = @InstanceId
ORDER BY samples.TestRunIntervalId
GO

GRANT EXECUTE ON Prc_GetSamplesForInstance TO PUBLIC
GO

CREATE PROCEDURE Prc_GetFirstSampleForInstance @LoadTestRunId int, @InstanceId int
AS
SELECT TOP 1
interval.IntervalEndTime,
samples.RawValue, 
samples.BaseValue, 
samples.CounterFrequency, 
samples.SystemFrequency,
samples.SampleTimeStamp,
samples.SampleTimeStamp100nSec,
samples.CounterType,
samples.ThresholdRuleResult
FROM LoadTestPerformanceCounterSample as samples
LEFT OUTER JOIN LoadTestRunInterval AS interval
    ON samples.LoadTestRunId = interval.LoadTestRunId
    AND samples.TestRunIntervalId = interval.TestRunIntervalId
WHERE samples.LoadTestRunId = @LoadTestRunId
AND samples.InstanceId = @InstanceId
ORDER BY samples.TestRunIntervalId
GO

GRANT EXECUTE ON Prc_GetFirstSampleForInstance TO PUBLIC
GO


CREATE PROCEDURE Prc_GetLastSampleForInstance @LoadTestRunId int, @InstanceId int
AS
SELECT TOP 1
interval.IntervalEndTime,
samples.RawValue, 
samples.BaseValue, 
samples.CounterFrequency, 
samples.SystemFrequency,
samples.SampleTimeStamp,
samples.SampleTimeStamp100nSec,
samples.CounterType,
samples.ThresholdRuleResult
FROM LoadTestPerformanceCounterSample as samples
LEFT OUTER JOIN LoadTestRunInterval AS interval
    ON samples.LoadTestRunId = interval.LoadTestRunId
    AND samples.TestRunIntervalId = interval.TestRunIntervalId
WHERE samples.LoadTestRunId = @LoadTestRunId
AND samples.InstanceId = @InstanceId
ORDER BY samples.TestRunIntervalId DESC
GO

GRANT EXECUTE ON Prc_GetLastSampleForInstance TO PUBLIC
GO


CREATE  PROCEDURE Prc_GetTransactionMap @LoadTestRunId int
AS
SELECT 
TransactionId,
TestCaseId,
TransactionName
FROM WebLoadTestTransaction
WHERE LoadTestRunId = @LoadTestRunId
ORDER BY TransactionId
GO

GRANT EXECUTE ON Prc_GetTransactionMap TO PUBLIC
GO

CREATE PROCEDURE Prc_InsertSample
	@LoadTestRunId int,
	@TestRunIntervalId int,
	@InstanceId int,
	@ComputedValue real,
	@RawValue bigint,
	@BaseValue bigint,
	@CounterFrequency bigint,
	@SystemFrequency bigint,
	@SampleTimeStamp bigint,
	@SampleTimeStamp100nSec bigint,
	@CounterType int,
	@ThresholdRuleResult tinyint,
	@ThresholdRuleMessageId int
AS
INSERT INTO LoadTestPerformanceCounterSample
(
	LoadTestRunId,
	TestRunIntervalId,
	InstanceId,
	ComputedValue,
	RawValue,
	BaseValue,
	CounterFrequency,
	SystemFrequency,
	SampleTimeStamp,
	SampleTimeStamp100nSec,
	CounterType,
	ThresholdRuleResult,
	ThresholdRuleMessageId
)
VALUES(
	@LoadTestRunId,
	@TestRunIntervalId,
	@InstanceId,
	@ComputedValue,
	@RawValue,
	@BaseValue,
	@CounterFrequency,
	@SystemFrequency,
	@SampleTimeStamp,
	@SampleTimeStamp100nSec,
	@CounterType,
	@ThresholdRuleResult,
	@ThresholdRuleMessageId
)
GO

GRANT EXECUTE ON Prc_InsertSample TO PUBLIC
GO

CREATE PROCEDURE Prc_InsertSqlTraceRow 
	@LoadTestRunId int,
	@TextData ntext,
	@Duration bigint,
	@StartTime datetime,
	@EndTime datetime,
	@Reads bigint,
	@Writes bigint,
	@CPU int,
	@EventClass int
AS
INSERT INTO LoadTestSqlTrace
(
	LoadTestRunId,
	TextData,
	Duration,
	StartTime,
	EndTime,
	Reads,
	Writes,
	CPU,
	EventClass
)
VALUES(
	@LoadTestRunId,
	@TextData,
	@Duration,
	@StartTime,
	@EndTime,
	@Reads,
	@Writes,
	@CPU,
	@EventClass
)
GO

GRANT EXECUTE ON Prc_InsertSqlTraceRow TO PUBLIC
GO
    
CREATE PROCEDURE Prc_UpdateInstanceSummaryData
	@LoadTestRunId int,
	@InstanceId int,
	@CumulativeValue float,
	@OverallThresholdRuleResult tinyint
AS
UPDATE LoadTestPerformanceCounterInstance
	SET CumulativeValue = @CumulativeValue,
	OverallThresholdRuleResult = @OverallThresholdRuleResult
	WHERE LoadTestRunId = @LoadTestRunId AND InstanceId = @InstanceId
GO

GRANT EXECUTE ON Prc_UpdateInstanceSummaryData TO PUBLIC
GO


CREATE PROCEDURE Prc_GetMessagesByRequest
	@LoadTestRunId int, @RequestId int
AS
SELECT 
AgentName,
ScenarioName,
TestCaseName,
RequestUri,
MessageId,
MessageTimeStamp,
MessageType,
SubType,
MessageText,
StackTrace,
HasWebTestErrorDetail
FROM LoadTestMessageView
WHERE
LoadTestRunId = @LoadTestRunId AND
RequestId = @RequestId
ORDER BY AgentName, MessageId
GO

GRANT EXECUTE ON Prc_GetMessagesByRequest TO PUBLIC
GO

CREATE PROCEDURE Prc_GetMessagesByAgent
	@LoadTestRunId int, @AgentName nvarchar(255)
AS
SELECT 
AgentName,
ScenarioName,
TestCaseName,
RequestUri,
MessageId,
MessageTimeStamp,
MessageType,
SubType,
MessageText,
StackTrace,
HasWebTestErrorDetail
FROM LoadTestMessageView
WHERE
LoadTestRunId = @LoadTestRunId AND
AgentName = @AgentName
ORDER BY AgentName, MessageId
GO

GRANT EXECUTE ON Prc_GetMessagesByAgent TO PUBLIC
GO


CREATE PROCEDURE Prc_GetMessagesByTest
	@LoadTestRunId int, @ScenarioName nvarchar(64), @TestCaseName nvarchar(64)
AS
SELECT 
AgentName,
ScenarioName,
TestCaseName,
RequestUri,
MessageId,
MessageTimeStamp,
MessageType,
SubType,
MessageText,
StackTrace,
HasWebTestErrorDetail
FROM LoadTestMessageView
WHERE
LoadTestRunId = @LoadTestRunId AND
ScenarioName = @ScenarioName AND
TestCaseName = @TestCaseName
ORDER BY AgentName, ScenarioName, TestCaseName
GO

GRANT EXECUTE ON Prc_GetMessagesByTest TO PUBLIC
GO


CREATE PROCEDURE Prc_GetMessagesByType
	@LoadTestRunId int, @MessageType tinyint, @SubType nvarchar(64)
AS
SELECT 
AgentName,
ScenarioName,
TestCaseName,
RequestUri,
MessageId,
MessageTimeStamp,
MessageType,
SubType,
MessageText,
StackTrace,
HasWebTestErrorDetail
FROM LoadTestMessageView
WHERE
LoadTestRunId = @LoadTestRunId AND
MessageType = @MessageType AND
SubType = @SubType
ORDER BY AgentName, MessageType, SubType
GO

GRANT EXECUTE ON Prc_GetMessagesByType TO PUBLIC
GO

CREATE PROCEDURE Prc_GetMessagesAll
	@LoadTestRunId int
AS
SELECT 
AgentName,
ScenarioName,
TestCaseName,
RequestUri,
MessageId,
MessageTimeStamp,
MessageType,
SubType,
MessageText,
StackTrace,
HasWebTestErrorDetail
FROM LoadTestMessageView
WHERE
LoadTestRunId = @LoadTestRunId
ORDER BY AgentName, MessageType, SubType
GO

GRANT EXECUTE ON Prc_GetMessagesAll TO PUBLIC
GO
CREATE PROCEDURE Prc_GetErrorDetail
	@LoadTestRunId int, @AgentName nvarchar(255), @MessageId int
AS
SELECT
WebTestRequestResult
FROM WebLoadTestErrorDetail as detail
LEFT OUTER JOIN LoadTestRunAgent AS agent
    ON detail.LoadTestRunId = agent.LoadTestRunId
    AND detail.AgentId = agent.AgentId
WHERE
detail.LoadTestRunId = @LoadTestRunId AND
AgentName = @AgentName AND
MessageId = @MessageId
GO

GRANT EXECUTE ON Prc_GetErrorDetail TO PUBLIC
GO

CREATE PROCEDURE Prc_GetTestCases @LoadTestRunId int
AS
SELECT 
testcase.TestCaseId, 
scenario.ScenarioName,
testcase.TestCaseName
FROM LoadTestCase AS testcase 
INNER JOIN LoadTestScenario As scenario
    ON testcase.LoadTestRunId = scenario.LoadTestRunId
    AND testcase.ScenarioId = scenario.ScenarioId
WHERE testcase.LoadTestRunId = @LoadTestRunId
ORDER BY testcase.TestCaseId
GO

GRANT EXECUTE ON Prc_GetTestCases TO PUBLIC
GO

-- New stored procedures

CREATE PROCEDURE Prc_InsertTestDetail
	@LoadTestRunId int,
	@TestDetailId int,
	@TimeStamp datetime,
	@TestCaseId int,
	@ElapsedTime float,
        @AgentId int,
        @BrowserId int,
        @NetworkId int
AS
INSERT INTO LoadTestTestDetail
(
	LoadTestRunId,
	TestDetailId,
	TimeStamp,
	TestCaseId,
	ElapsedTime,
        AgentId,
        BrowserId,
        NetworkId
)
VALUES(
	@LoadTestRunId,
	@TestDetailId,
	@TimeStamp,
	@TestCaseId,
	@ElapsedTime,
        @AgentId,
        @BrowserId,
        @NetworkId
)
GO
GRANT EXECUTE ON Prc_InsertTestDetail TO PUBLIC
GO

CREATE PROCEDURE Prc_InsertTransactionDetail
	@LoadTestRunId int,
	@TransactionDetailId int,
	@TestDetailId int,
	@TimeStamp datetime,
	@TransactionId int,
	@ElapsedTime float
AS
INSERT INTO LoadTestTransactionDetail
(
	LoadTestRunId,
	TransactionDetailId,
	TestDetailId,
	TimeStamp,
	TransactionId,
	ElapsedTime
)
VALUES(
	@LoadTestRunId,
	@TransactionDetailId,
	@TestDetailId,
	@TimeStamp,
	@TransactionId,
	@ElapsedTime
)
GO
GRANT EXECUTE ON Prc_InsertTransactionDetail TO PUBLIC
GO

CREATE PROCEDURE Prc_InsertPageDetail
	@LoadTestRunId int,
	@PageDetailId int,
	@TestDetailId int,
	@TimeStamp datetime,
	@PageId int,
	@ResponseTime float,
	@ResponseTimeGoal float,
	@GoalExceeded bit
AS
INSERT INTO LoadTestPageDetail
(
	LoadTestRunId,
	PageDetailId,
	TestDetailId,
	TimeStamp,
	PageId,
	ResponseTime,
	ResponseTimeGoal,
	GoalExceeded
)
VALUES(
	@LoadTestRunId,
	@PageDetailId,
	@TestDetailId,
	@TimeStamp,
	@PageId,
	@ResponseTime,
	@ResponseTimeGoal,
	@GoalExceeded
)
GO
GRANT EXECUTE ON Prc_InsertPageDetail TO PUBLIC
GO

CREATE PROCEDURE Prc_UpdateTestPercentiles @LoadTestRunId int, @TestCaseId int
AS
update LoadTestTestSummaryData set 
    Percentile90 =
    (select min(ElapsedTime) from 
    (select top 10 percent ElapsedTime from LoadTestTestDetail 
        where LoadTestRunId = @LoadTestRunId and TestCaseId=@TestCaseId
        order by ElapsedTime desc) as TopElapsedTimes),
    Percentile95 =
    (select min(ElapsedTime) from 
    (select top 5 percent ElapsedTime from LoadTestTestDetail 
        where LoadTestRunId = @LoadTestRunId and TestCaseId=@TestCaseId
        order by ElapsedTime desc) as TopElapsedTimes)
where LoadTestRunId = @LoadTestRunId and TestCaseId=@TestCaseId
GO
GRANT EXECUTE ON Prc_UpdateTestPercentiles TO PUBLIC
GO

CREATE PROCEDURE Prc_UpdateTransactionPercentiles @LoadTestRunId int, @TransactionId int
AS
update LoadTestTransactionSummaryData set 
    Percentile90 =
    (select min(ElapsedTime) from 
    (select top 10 percent ElapsedTime from LoadTestTransactionDetail 
        where LoadTestRunId = @LoadTestRunId and TransactionId=@TransactionId
        order by ElapsedTime desc) as TopElapsedTimes),
    Percentile95 =
    (select min(ElapsedTime) from 
    (select top 5 percent ElapsedTime from LoadTestTransactionDetail 
        where LoadTestRunId = @LoadTestRunId and TransactionId=@TransactionId
        order by ElapsedTime desc) as TopElapsedTimes)
where LoadTestRunId = @LoadTestRunId and TransactionId=@TransactionId
GO
GRANT EXECUTE ON Prc_UpdateTransactionPercentiles TO PUBLIC
GO

CREATE PROCEDURE Prc_UpdatePagePercentiles @LoadTestRunId int, @PageId int
AS
update LoadTestPageSummaryData set 
    Percentile90 =
    (select min(ResponseTime) from 
    (select top 10 percent ResponseTime from LoadTestPageDetail 
        where LoadTestRunId = @LoadTestRunId and PageId=@PageId
        order by ResponseTime desc) as TopResponseTimes),
    Percentile95 =
    (select min(ResponseTime) from 
    (select top 5 percent ResponseTime from LoadTestPageDetail 
        where LoadTestRunId = @LoadTestRunId and PageId=@PageId
        order by ResponseTime desc) as TopResponseTimes)
where LoadTestRunId = @LoadTestRunId and PageId=@PageId
GO
GRANT EXECUTE ON Prc_UpdatePagePercentiles TO PUBLIC
GO

CREATE PROCEDURE Prc_UpdatePageByNetworkPercentiles @LoadTestRunId int, @PageId int, @NetworkId int
AS
update LoadTestPageSummaryByNetwork set 
    Percentile90 =
    (select min(ResponseTime) from 
    (select top 10 percent pageDetail.ResponseTime 
        from LoadTestPageDetail as pageDetail
        INNER JOIN LoadTestTestDetail as testDetail
	    ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
	    AND pageDetail.TestDetailId = testDetail.TestDetailId
        where pageDetail.LoadTestRunId = @LoadTestRunId and 
              pageDetail.PageId=@PageId and
              testDetail.NetworkId=@NetworkId
        order by pageDetail.ResponseTime desc) as TopResponseTimes),
    Percentile95 =
    (select min(ResponseTime) from 
    (select top 5 percent pageDetail.ResponseTime 
        from LoadTestPageDetail as pageDetail
        INNER JOIN LoadTestTestDetail as testDetail
	    ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
	    AND pageDetail.TestDetailId = testDetail.TestDetailId
        where pageDetail.LoadTestRunId = @LoadTestRunId and 
              pageDetail.PageId=@PageId and
              testDetail.NetworkId=@NetworkId
        order by pageDetail.ResponseTime desc) as TopResponseTimes),
    PagesMeetingGoal =
       (select count(*)
        from LoadTestPageDetail as pageDetail
	    INNER JOIN LoadTestTestDetail as testDetail
	    ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
	    AND pageDetail.TestDetailId = testDetail.TestDetailId
        where pageDetail.LoadTestRunId = @LoadTestRunId and 
              pageDetail.PageId=@PageId and
              testDetail.NetworkId=@NetworkId and
              pageDetail.GoalExceeded = 0)
where LoadTestRunId=@LoadTestRunId and PageId=@PageId and NetworkId=@NetworkId           
GO
GRANT EXECUTE ON Prc_UpdatePageByNetworkPercentiles TO PUBLIC
GO

CREATE PROCEDURE Prc_UpdateSummaryData @LoadTestRunId int, @DeleteDetailTables bit
AS
BEGIN
	INSERT INTO LoadTestTestSummaryData 
	    (LoadTestRunId, TestCaseId, TestsRun, Average, Minimum, Maximum)
	SELECT LoadTestRunId, TestCaseId,
	    count(*) as TestsRun,
	    avg(ElapsedTime) as Average, 
	    min(ElapsedTime) as Minimum,
	    max(ElapsedTime) as Maximum
	    FROM LoadTestTestDetail where LoadTestRunId=@LoadTestRunId
	    GROUP BY LoadTestRunId, TestCaseId

	DECLARE @TestCaseId int

	DECLARE TestCaseCursor CURSOR FOR
	SELECT DISTINCT(TestCaseId) FROM LoadTestTestDetail WHERE LoadTestRunId = @LoadTestRunId

	OPEN TestCaseCursor
	FETCH NEXT FROM TestCaseCursor INTO @TestCaseId

	WHILE @@FETCH_STATUS = 0
	BEGIN
	   EXEC Prc_UpdateTestPercentiles @LoadTestRunId, @TestCaseId
	   FETCH NEXT FROM TestCaseCursor INTO @TestCaseId
	END

	CLOSE TestCaseCursor
	DEALLOCATE TestCaseCursor

	INSERT INTO LoadTestTransactionSummaryData 
	    (LoadTestRunId, TransactionId, TransactionCount, Average, Minimum, Maximum)
	SELECT LoadTestRunId, TransactionId,
	    count(*) as TransactionCount,
	    avg(ElapsedTime) as Average, 
	    min(ElapsedTime) as Minimum,
	    max(ElapsedTime) as Maximum
	    FROM LoadTestTransactionDetail where LoadTestRunId=@LoadTestRunId
	    GROUP BY LoadTestRunId, TransactionId

	DECLARE @TransactionId int

	DECLARE TransactionIdCursor CURSOR FOR
	SELECT DISTINCT(TransactionId) FROM LoadTestTransactionDetail WHERE LoadTestRunId = @LoadTestRunId

	OPEN TransactionIdCursor
	FETCH NEXT FROM TransactionIdCursor INTO @TransactionId

	WHILE @@FETCH_STATUS = 0
	BEGIN
	   EXEC Prc_UpdateTransactionPercentiles @LoadTestRunId, @TransactionId
	   FETCH NEXT FROM TransactionIdCursor INTO @TransactionId
	END

	CLOSE TransactionIdCursor
	DEALLOCATE TransactionIdCursor

	INSERT INTO LoadTestPageSummaryData 
	    (LoadTestRunId, PageId, PageCount, Average, Minimum, Maximum)
	SELECT LoadTestRunId, PageId,
	    count(*) as PageCount,
	    avg(ResponseTime) as Average, 
	    min(ResponseTime) as Minimum,
	    max(ResponseTime) as Maximum
	    FROM LoadTestPageDetail where LoadTestRunId=@LoadTestRunId
	    GROUP BY LoadTestRunId, PageId

	DECLARE @PageId int

	DECLARE PageIdCursor CURSOR FOR
	SELECT DISTINCT(PageId) FROM LoadTestPageDetail WHERE LoadTestRunId = @LoadTestRunId

	OPEN PageIdCursor
	FETCH NEXT FROM PageIdCursor INTO @PageId

	WHILE @@FETCH_STATUS = 0
	BEGIN
	   EXEC Prc_UpdatePagePercentiles @LoadTestRunId, @PageId
	   FETCH NEXT FROM PageIdCursor INTO @PageId
	END

	CLOSE PageIdCursor
	DEALLOCATE PageIdCursor

	INSERT INTO LoadTestPageSummaryByNetwork
	    (LoadTestRunId, PageId, NetworkId, PageCount, Average, Minimum, Maximum, Goal)
	SELECT pageDetail.LoadTestRunId, pageDetail.PageId, testDetail.NetworkId,
	    count(*) as PageCount,
	    avg(ResponseTime) as Average, 
	    min(ResponseTime) as Minimum,
	    max(ResponseTime) as Maximum,
	    avg(ResponseTimeGoal) as Goal
	    FROM LoadTestPageDetail as pageDetail
	    INNER JOIN LoadTestTestDetail as testDetail
	    ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
	    AND pageDetail.TestDetailId = testDetail.TestDetailId
	    WHERE pageDetail.LoadTestRunId = @LoadTestRunId
	    GROUP BY pageDetail.LoadTestRunId, PageId, testDetail.NetworkId

	DECLARE @NetworkId int

	DECLARE PageNetworkCursor CURSOR FOR
	SELECT PageId, NetworkId from LoadTestPageSummaryByNetwork WHERE LoadTestRunId = @LoadTestRunId

	OPEN PageNetworkCursor
	FETCH NEXT FROM PageNetworkCursor INTO @PageId, @NetworkId

	WHILE @@FETCH_STATUS = 0
	BEGIN
	   EXEC Prc_UpdatePageByNetworkPercentiles @LoadTestRunId, @PageId, @NetworkId
	   FETCH NEXT FROM PageNetworkCursor INTO @PageId, @NetworkId
	END

	CLOSE PageNetworkCursor
	DEALLOCATE PageNetworkCursor

	IF @DeleteDetailTables = 1
	BEGIN
		DELETE from LoadTestTestDetail where LoadTestRunId = @LoadTestRunId
		DELETE from LoadTestTransactionDetail where LoadTestRunId = @LoadTestRunId
		DELETE from LoadTestPageDetail where LoadTestRunId = @LoadTestRunId
	END
END
GO
GRANT EXECUTE ON Prc_UpdateSummaryData TO PUBLIC
GO

CREATE PROCEDURE prc_GrantDBAccess
@dbUser SYSNAME	
AS
-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

DECLARE @status INT

-- First grant the user the ability to login to SQL server
EXEC @status = sp_grantlogin @dbUser

IF (@status <> 0)
BEGIN
    RAISERROR (500001, 16, -1, @procedureName, 'Unable to grant login to user')
    RETURN @status
END

-- Grant the user access to this database if not already enabled
-- either by an explicit grant or by being the database owner
IF NOT (
    EXISTS (
        SELECT  name 
        FROM    sysusers 
        WHERE   name = @dbUser)
    OR EXISTS (
        SELECT  sid
        FROM    master.dbo.sysdatabases
        WHERE   dbid = db_id()
                AND sid = SUSER_SID(@dbUser)))
BEGIN
    EXEC @status = sp_grantdbaccess @dbUser
    IF (@status <> 0)
    BEGIN
        RAISERROR (500001, 16, -1, @procedureName, 'Unable to grant access to user')
        RETURN @status
    END
END
RETURN 
GO

--If we are running on SQLExpress, we need to grant DBAccess to all local users
IF @@servername LIKE '%\SQLEXPRESS'
BEGIN
	-- If running on SQLEXPRESS grant access to
	-- BUILTIN\Users group
	EXEC PRC_grantDBAccess [BUILTIN\Users]
END
GO

-- WebLoadTestTransactionContent has the complete query text. 
CREATE TABLE [dbo].[WebLoadTestTransactionContent](
    [LoadTestRunId] [int] NOT NULL,
	[TransactionName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TransactionText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE PROC [dbo].[InsertTrans]
	@TransactionName nvarchar(255),
	@TransactionText ntext
AS
	DECLARE @LoadTestRunId int
	SELECT @LoadTestRunId = MAX(LoadTestRunId)
	FROM LoadTestRun

	DECLARE @rowCount int
	SELECT @rowCount = COUNT(*)
	FROM [dbo].[WebLoadTestTransactionContent] 
	WHERE LoadTestRunId = @LoadTestRunId AND TransactionName = @TransactionName
	
	IF @rowCount = 0 
	BEGIN
		INSERT INTO [dbo].[WebLoadTestTransactionContent] 
		(LoadTestRunId, TransactionName, TransactionText)
		VALUES
		(@LoadTestRunId, @TransactionName, @TransactionText)
	END